#!/usr/bin/env python
# -*- coding: UTF-8 -*-
"""
@Project    :icac2 
@File       :mysql_utils.py
@Author     :wangfan13
@Date       :2021/5/28 9:55 
"""
import pymssql
import pymysql
from pymysql.cursors import DictCursor
from service.settings import DATABASES
from django.conf import settings


class DBUtil:
    """mysql util"""
    db = None
    cursor = None

    def __init__(self):
        self.host = DATABASES.get("default").get("HOST", "172.17.240.153")
        self.port = int(DATABASES.get("default").get("PORT", 3306))
        self.userName = DATABASES.get("default").get("USER", 3306)
        self.password = DATABASES.get("default").get("PASSWORD", "root")
        self.dbName = DATABASES.get("default").get("NAME", "icac")
        self.charsets = 'utf8'

    # 链接数据库
    def get_con(self):
        """ 获取conn """
        self.db = pymysql.Connect(
            host=self.host,
            port=self.port,
            user=self.userName,
            passwd=self.password,
            db=self.dbName,
            charset=self.charsets
        )
        self.cursor = self.db.cursor()
        # 返回字典
        # self.cursor = self.db.cursor(cursor=DictCursor)

    # 关闭链接
    def close(self):
        self.cursor.close()
        self.db.close()

    # 主键查询数据
    def get_one(self, sql):
        res = None
        try:
            self.get_con()
            self.cursor.execute(sql)
            res = self.cursor.fetchone()
            self.close()
        except Exception as e:
            print("查询失败！" + str(e))
        return res

    # 查询列表数据
    def get_all(self, sql):
        res = None
        try:
            self.get_con()
            self.cursor.execute(sql)
            res = self.cursor.fetchall()
            self.close()
        except Exception as e:
            print("查询失败！" + str(e))
        return res

    # 插入数据
    def __insert(self, sql):
        count = 0
        try:
            self.get_con()
            count = self.cursor.execute(sql)
            self.db.commit()
            self.close()
        except Exception as e:
            print("操作失败！" + str(e))
            self.db.rollback()
        return count

    # 保存数据
    def save(self, sql):
        return self.__insert(sql)

    # 更新数据
    def update(self, sql):
        return self.__insert(sql)

    # 删除数据
    def delete(self, sql):
        return self.__insert(sql)


class DBUtil_Dict:
    """mysql util"""
    db = None
    cursor = None

    def __init__(self):
        self.host = DATABASES.get("default").get("HOST", "172.17.240.153")
        self.port = int(DATABASES.get("default").get("PORT", 3306))
        self.userName = DATABASES.get("default").get("USER", 'root')
        self.password = DATABASES.get("default").get("PASSWORD", "root")
        self.dbName = DATABASES.get("default").get("NAME", "icac")
        self.charsets = 'utf8'

    # 链接数据库
    def get_con(self):
        """ 获取conn """
        self.db = pymysql.Connect(
            host=self.host,
            port=self.port,
            user=self.userName,
            passwd=self.password,
            db=self.dbName,
            charset=self.charsets
        )
        # self.cursor = self.db.cursor()
        # 返回字典
        self.cursor = self.db.cursor(cursor=DictCursor)

    # 关闭链接
    def close(self):
        self.cursor.close()
        self.db.close()

    # 主键查询数据
    def get_one(self, sql):
        res = None
        try:
            self.get_con()
            self.cursor.execute(sql)
            res = self.cursor.fetchone()
            self.close()
        except Exception as e:
            print("查询失败！" + str(e))
        return res

    # 查询列表数据
    def get_all(self, sql):
        res = None
        try:
            self.get_con()
            self.cursor.execute(sql)
            res = self.cursor.fetchall()
            self.close()
        except Exception as e:
            print("查询失败！" + str(e))
        return res

    # 插入数据
    def __insert(self, sql):
        count = 0
        try:
            self.get_con()
            count = self.cursor.execute(sql)
            self.db.commit()
            self.close()
        except Exception as e:
            print("操作失败！" + str(e))
            self.db.rollback()
        return count

    # 保存数据
    def save(self, sql):
        return self.__insert(sql)

    # 更新数据
    def update(self, sql):
        return self.__insert(sql)

    # 删除数据
    def delete(self, sql):
        return self.__insert(sql)


DBUtils = DBUtil()


class SQLServerDBUtil:
    """mysql util"""
    db = None
    cursor = None

    def __init__(self):
        self.host = settings.MSSQL_HOST,
        self.user = settings.MSSQL_USER,
        self.password = settings.MSSQL_PASSWORD,
        self.database = settings.MSSQL_DATABASE
        self.charsets = 'utf8'

    # 链接数据库
    def get_con(self):
        """ 获取conn """
        self.db = pymssql.connect(
            host=self.host,
            user=self.user,
            password=self.password,
            database=self.database
        )
        # self.cursor = self.db.cursor()
        # 返回字典
        self.cursor = self.db.cursor()

    # 关闭链接
    def close(self):
        self.cursor.close()
        self.db.close()

    # 主键查询数据
    def get_one(self, sql):
        res = None
        try:
            self.get_con()
            self.cursor.execute(sql)
            res = self.cursor.fetchone()
            self.close()
        except Exception as e:
            print("查询失败！" + str(e))
        return res

    # 查询列表数据
    def get_all(self, sql):
        res = None
        try:
            self.get_con()
            self.cursor.execute(sql)
            res = self.cursor.fetchall()
            self.close()
        except Exception as e:
            print("查询失败！" + str(e))
        return res

    # 插入数据
    def __insert(self, sql):
        count = 0
        try:
            self.get_con()
            count = self.cursor.execute(sql)
            self.db.commit()
            self.close()
        except Exception as e:
            print("操作失败！" + str(e))
            self.db.rollback()
        return count

    # 保存数据
    def save(self, sql):
        return self.__insert(sql)

    # 更新数据
    def update(self, sql):
        return self.__insert(sql)

    # 删除数据
    def delete(self, sql):
        return self.__insert(sql)
